/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package jamjun.infra.common;

import jamjun.infra.lang.JJDate;
import jamjun.infra.lang.JJString;
import jamjun.infra.model.DBConfig;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

/**
 *
 * @author Java
 */
public class DBHelper {
    
    public static Connection getConnection(String conStringName) throws SQLException, IOException, ParserConfigurationException, SAXException, ClassNotFoundException, Exception{
        try{
            Connection con = null;
            DBConfig dbConfig = new AppConfig().getDBConnection(conStringName);
            Class.forName (dbConfig.db_driver);
            con = DriverManager.getConnection(dbConfig.db_url,dbConfig.db_user,dbConfig.db_password);
            return con;
        }
        catch(IOException | ClassNotFoundException | SQLException e)
        {
            throw e;
        }
    }
    
    public static ResultSet executeQuery(String sqlCommand,List<SqlParameter> sqlParameter) throws Exception{
        try{
            ResultSet rs = null;
                
            return rs;
        }catch(Exception e){
            throw new Exception(e);
        }
    }
    
    public static PreparedStatement setPreparedStatement(Connection conn,String sqlCommand,List<SqlParameter> sqlParameterList) throws Exception{
        try{
            PreparedStatement pstmt = null;
            String sql = sqlCommand;
            String sqltemp = sqlCommand;
            int index = 0;
            for(SqlParameter sqlParameter : sqlParameterList){
                sql = sql.replace(sqlParameter.parameterName,"?");
                index=sqltemp.indexOf(sqlParameter.parameterName);
                sqlParameter.index = index;
            }
            sqlParameterList = sqlParameterList.stream().sorted((s1,s2) -> s1.index.compareTo(s2.index)).collect(Collectors.toList());
            //set PreparedStatement
            //check index out of range
            
            int size = JJString.SearchCount("?",sql);
            if(size != sqlParameterList.size()){
                throw new Exception("error : sql index out of range");
            }
            
            pstmt = conn.prepareStatement(sql); // create a statement
            int i = 0;
            for(SqlParameter sqlParameter : sqlParameterList){
                i++;
                if(sqlParameter.dataType == DataType.typeString){
                    String value = "";
                    if(sqlParameter.value != null){
                        value = sqlParameter.value.toString();
                    }
                    pstmt.setString(i,value);
                }else if(sqlParameter.dataType == DataType.typeInt){
                    if(sqlParameter.value == null){
                        pstmt.setInt(i,0);
                    }else{
                        pstmt.setInt(i,(int) sqlParameter.value);
                    }
                }else if(sqlParameter.dataType == DataType.typeDouble){
                    if(sqlParameter.value == null){
                        pstmt.setDouble(i,0);
                    }else{
                        pstmt.setDouble(i,(double) sqlParameter.value);
                    }
                }else if(sqlParameter.dataType == DataType.typeByte){
                    pstmt.setByte(i,(byte) sqlParameter.value);
                }else if(sqlParameter.dataType == DataType.typeFloat){
                    try{ 
                        if(sqlParameter.value == null){
                            pstmt.setFloat(i,0);
                        }else{
                            pstmt.setFloat(i,(float) sqlParameter.value);
                        }
                    }catch(SQLException ex){
                        throw ex;
                    }
                }else if(sqlParameter.dataType == DataType.typeDate){
                    if(sqlParameter.value == null){
                            pstmt.setDate(i,null);
                    }else{
                        Date date = (Date) sqlParameter.value;
                        java.sql.Date sqlDate = new java.sql.Date(date.getTime()); 
                        pstmt.setDate(i,sqlDate);
                    }
                }else if(sqlParameter.dataType == DataType.typeDateTime){
                    try{
                        if(sqlParameter.value == null){
                            pstmt.setTimestamp(i,null);
                        }else{
                            Date date = (Date) sqlParameter.value;
                            java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(date.getTime());
                            pstmt.setTimestamp(i, sqlTimestamp);
                        }
                    }catch(SQLException ex){
                        throw ex;
                    }
                }else if(sqlParameter.dataType == DataType.typeLong){
                        if(sqlParameter.value == null){
                            pstmt.setLong(i,0);
                        }else{
                            pstmt.setLong(i,(long) sqlParameter.value);
                        }
                }else if(sqlParameter.dataType == DataType.typeBoolean){
                        if(sqlParameter.value == null){
                            pstmt.setBoolean(i,false);
                        }else{
                            pstmt.setBoolean(i,(boolean) sqlParameter.value);
                        }
                }
            }
            
            return pstmt;
        }catch(SQLException e){
            throw e;
        }
    }
}
